import os
from flask import Flask, render_template, request, jsonify, session, redirect, url_for, send_file
from Python.email_utils import send_verification_code, generate_code, is_valid_email
from werkzeug.security import generate_password_hash, check_password_hash
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime, timedelta
import threading
import pandas as pd
import numpy as np
from io import BytesIO
import logging
import json

app = Flask(__name__)
app.secret_key = 'demo'
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://aocjor:rCrv9PFfCoLpplP0@mysql5.sqlpub.com:3310/lzbr_information'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
    'pool_pre_ping': True,
    'pool_recycle': 300,
    'pool_size': 10,  # 增加连接池大小
    'max_overflow': 20,  # 增加最大溢出连接数
    'pool_timeout': 30,  # 设置获取连接的超时时间
    'pool_reset_on_return': 'rollback'  # 连接返回时回滚事务
}

db = SQLAlchemy(app)

# 全局变量存储数据缓存
_data_cache = {}
_cache_timestamp = {}
CACHE_DURATION = 1800  # 缓存30分钟
_cache_initialized = False
_init_lock = threading.Lock()

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    password = db.Column(db.String(255), nullable=False)
    email = db.Column(db.String(100), unique=True, nullable=False)

# 全局数据获取函数 - 带缓存机制
def get_cached_sales_data():
    """获取缓存的销售数据，如果缓存过期则重新加载"""
    current_time = datetime.now().timestamp()
    
    # 检查缓存是否存在且未过期
    if ('sales_data' in _data_cache and 
        'sales_data' in _cache_timestamp and 
        current_time - _cache_timestamp['sales_data'] < CACHE_DURATION):
        
        # 计算缓存剩余时间
        remaining_time = CACHE_DURATION - (current_time - _cache_timestamp['sales_data'])
        app.logger.info(f"使用缓存的销售数据，剩余有效时间: {remaining_time:.1f}秒")
        return _data_cache['sales_data']
    
    try:
        if 'sales_data' in _cache_timestamp:
            elapsed_time = current_time - _cache_timestamp['sales_data']
            app.logger.info(f"缓存已过期 ({elapsed_time:.1f}秒)，重新加载销售数据...")
        else:
            app.logger.info("首次加载销售数据...")
            
        # 使用Flask-SQLAlchemy的引擎执行查询
        query = "SELECT * FROM car_sales"
        df = pd.read_sql(query, db.engine)
        
        # 字段名映射
        df = df.rename(columns={
            'year': '年份',
            'month': '月份', 
            'ranking': '排名',
            'model': '车型',
            'manufacturer': '厂商',
            'sales_volume': '销量',
            'price_range': '售价(万元)'
        })
        
        # 数据清洗
        df['销量'] = pd.to_numeric(df['销量'], errors='coerce')
        df['年份'] = pd.to_numeric(df['年份'], errors='coerce')
        df['月份'] = pd.to_numeric(df['月份'], errors='coerce')
        df = df[df['销量'] > 0]  # 过滤无效数据
        df['日期'] = df['年份'].astype(str) + '-' + df['月份'].astype(str).str.zfill(2)
        
        # 更新缓存
        _data_cache['sales_data'] = df
        _cache_timestamp['sales_data'] = current_time
        
        app.logger.info(f"销售数据加载完成，共 {len(df)} 条记录，下次更新时间: {datetime.fromtimestamp(current_time + CACHE_DURATION).strftime('%H:%M:%S')}")
        return df
        
    except Exception as e:
        app.logger.error(f"加载销售数据失败: {str(e)}")
        # 如果有旧缓存，返回旧缓存
        if 'sales_data' in _data_cache:
            app.logger.warning("使用过期的缓存数据")
            return _data_cache['sales_data']
        raise e

# 预热缓存的函数
def warm_up_cache():
    """应用启动时预热数据缓存"""
    global _cache_initialized
    
    with _init_lock:
        if _cache_initialized:
            app.logger.info("缓存已经初始化，跳过重复加载")
            return
            
        try:
            app.logger.info("开始预热数据缓存...")
            get_cached_sales_data()
            _cache_initialized = True
            app.logger.info("数据缓存预热完成")
        except Exception as e:
            app.logger.error(f"数据缓存预热失败: {str(e)}")

@app.route('/')
def home():
    return redirect(url_for('login'))

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')

        # 验证输入
        if not username or not password:
            return jsonify({
                'success': False,
                'message': '用户名和密码不能为空!'
            })

        user = User.query.filter_by(username=username).first()

        if user:
            if check_password_hash(user.password, password):
                # 设置会话
                session['user_id'] = user.id
                session['username'] = user.username

                # 返回重定向信息
                return jsonify({
                    'success': True,
                    'redirect_url': url_for('dashboard')
                })

        # 用户名或密码错误
        return jsonify({
            'success': False,
            'message': '用户名或密码错误!'
        })

    # GET 请求渲染登录页面
    return render_template('login.html')


import time
@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'GET':
        return render_template('register.html')
    
    if request.method == 'POST':
        action = request.form.get('action')
        
        # 处理发送验证码请求
        if action == 'send_code':
            email = request.form.get('email')
            
            if not email:
                return jsonify({'success': False, 'message': '请输入邮箱地址'})
            
            # 验证邮箱格式
            if not is_valid_email(email):
                return jsonify({'success': False, 'message': '邮箱格式不正确'})
            
            # 检查邮箱是否已注册 - 使用Flask-SQLAlchemy的方式
            existing_user = User.query.filter_by(email=email).first()
            if existing_user:
                return jsonify({'success': False, 'message': '该邮箱已被注册'})
            
            # 生成验证码
            code = generate_code()
            
            # 存储验证码（这里用session，实际项目建议用Redis）
            session[f'register_code_{email}'] = {
                'code': code,
                'timestamp': time.time()
            }
            
            # 发送验证码
            if send_verification_code(email, code):
                return jsonify({'success': True, 'message': '验证码已发送到您的邮箱'})
            else:
                return jsonify({'success': False, 'message': '验证码已发送到您的邮箱'})
        
        # 处理完成注册请求
        elif action == 'verify_and_register':
            # 获取所有必需的字段
            username = request.form.get('username')
            email = request.form.get('email')
            password = request.form.get('password')
            code = request.form.get('code')
            
            # 验证所有字段都存在
            if not all([username, email, password, code]):
                return jsonify({'success': False, 'message': '请填写所有必需字段'})
            
            # 验证用户名长度
            if len(username) < 3 or len(username) > 20:
                return jsonify({'success': False, 'message': '用户名长度必须在3-20个字符之间'})
            
            # 验证密码长度
            if len(password) < 8:
                return jsonify({'success': False, 'message': '密码长度至少8个字符'})
            
            # 验证邮箱格式
            if not is_valid_email(email):
                return jsonify({'success': False, 'message': '邮箱格式不正确'})
            
            # 验证验证码
            stored_data = session.get(f'register_code_{email}')
            if not stored_data:
                return jsonify({'success': False, 'message': '验证码已过期，请重新发送'})
            
            # 检查验证码是否过期（10分钟）
            if time.time() - stored_data['timestamp'] > 600:
                session.pop(f'register_code_{email}', None)
                return jsonify({'success': False, 'message': '验证码已过期，请重新发送'})
            
            # 验证码是否正确
            if stored_data['code'] != code:
                return jsonify({'success': False, 'message': '验证码不正确'})
            
            # 检查用户名是否已存在 - 使用Flask-SQLAlchemy的方式
            existing_username = User.query.filter_by(username=username).first()
            if existing_username:
                return jsonify({'success': False, 'message': '用户名已存在'})
            
            # 检查邮箱是否已注册 - 使用Flask-SQLAlchemy的方式
            existing_email = User.query.filter_by(email=email).first()
            if existing_email:
                return jsonify({'success': False, 'message': '该邮箱已被注册'})
            
            try:
                # 生成密码哈希
                hashed_password = generate_password_hash(password)
                
                # 创建新用户 - 使用Flask-SQLAlchemy的方式
                new_user = User(
                    username=username,
                    email=email,
                    password=hashed_password
                )
                
                # 添加到数据库
                db.session.add(new_user)
                db.session.commit()
                
                # 清除验证码
                session.pop(f'register_code_{email}', None)
                
                return jsonify({'success': True, 'message': '注册成功！'})
                
            except Exception as e:
                db.session.rollback()
                app.logger.error(f"注册失败: {str(e)}")
                return jsonify({'success': False, 'message': '注册失败，请重试'})
        
        # 处理旧版本的直接注册
        else:
            username = request.form.get('username')
            email = request.form.get('email')
            password = request.form.get('password')
            
            # 基本验证
            if not all([username, email, password]):
                return jsonify({'success': False, 'message': '请填写所有字段'})
            
            return jsonify({'success': False, 'message': '请先进行邮箱验证'})

def async_send_email(email, code):
    """异步发送邮件的线程函数"""
    try:
        send_verification_code(email, code)
    except Exception as e:
        print(f"异步邮件发送失败: {str(e)}")

@app.route('/forgot_password', methods=['GET', 'POST'])
def forgot_password():
    if request.method == 'POST':
        action = request.form.get('action')
        email = request.form.get('email')

        if action == 'send_code':
            if not is_valid_email(email):
                return jsonify({'success': False, 'message': '邮箱格式无效!'})

            # 检查邮箱是否存在
            if not User.query.filter_by(email=email).first():
                return jsonify({'success': False, 'message': '邮箱未注册!'})

            # 生成并发送验证码
            code = generate_code()
            session['reset_code'] = code
            session['reset_email'] = email
            session['reset_expire'] = (datetime.now() + timedelta(minutes=10)).isoformat()

            # 异步发送邮件
            threading.Thread(target=async_send_email, args=(email, code)).start()

            return jsonify({'success': True, 'message': '验证码已发送!'})

        elif action == 'reset_password':
            user_code = request.form.get('code')
            new_password = request.form.get('new_password')

            # 验证码校验
            stored_code = session.get('reset_code')
            stored_email = session.get('reset_email')
            expire_time = session.get('reset_expire')

            if not stored_code or not expire_time:
                return jsonify({'success': False, 'message': '请先获取验证码!'})

            if datetime.now() > datetime.fromisoformat(expire_time):
                return jsonify({'success': False, 'message': '验证码已过期!'})

            if user_code != stored_code:
                return jsonify({'success': False, 'message': '验证码错误!'})

            # 更新密码
            user = User.query.filter_by(email=stored_email).first()
            if user:
                user.password = generate_password_hash(new_password)
                try:
                    db.session.commit()
                    # 清除session中的重置信息
                    session.pop('reset_code', None)
                    session.pop('reset_email', None)
                    session.pop('reset_expire', None)
                    return jsonify({'success': True, 'message': '密码重置成功!'})
                except Exception as e:
                    db.session.rollback()
                    return jsonify({'success': False, 'message': f'密码重置失败: {str(e)}'})
            else:
                return jsonify({'success': False, 'message': '用户不存在!'})

    return render_template('forgot_password.html')
@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('login'))

@app.route('/dashboard')
def dashboard():
    if 'user_id' not in session:
        return redirect(url_for('login'))
    return render_template('dashboard.html', username=session['username'])


#总览表
@app.route('/get_chart_data')
def get_chart_data():
    try:
        # 使用优化后的数据获取函数
        from Python.car_total_chart import get_chart_data_optimized
        chart_data = get_chart_data_optimized(get_cached_sales_data()) #读取启动时缓存好的MySQL表
        return jsonify(chart_data)
    except Exception as e:
        app.logger.error(f"获取图表数据失败: {str(e)}")
        return jsonify({'error': '无法获取图表数据'}), 500

# 优化后的API路由
@app.route('/api/manufacturer-data')
def manufacturer_data():
    manufacturer = request.args.get('manufacturer', 'all')
    year = request.args.get('year', '2023')
    try:
        from Python.manufacturer_data import get_manufacturer_data_optimized
        result = get_manufacturer_data_optimized(get_cached_sales_data(), manufacturer, year)
        return jsonify(result)
    except Exception as e:
        app.logger.error(f"获取厂商数据失败: {str(e)}")
        return jsonify({'success': False, 'error': f'获取厂商数据失败: {str(e)}'}), 500

@app.route('/api/export-data')
def export_data():
    try:
        # 获取请求参数
        manufacturers = request.args.getlist('manufacturers[]')
        year_range = request.args.get('year_range')

        from Python.export_data import export_sales_data_optimized
        return export_sales_data_optimized(get_cached_sales_data(), manufacturers, year_range)

    except Exception as e:
        app.logger.error(f"导出数据失败: {str(e)}")
        return jsonify({"success": False, "error": f"导出数据失败: {str(e)}"}), 500

@app.route('/api/ranking-data')
def ranking_data():
    year = request.args.get('year', default=None, type=int)
    try:
        from Python.ranking_data import get_ranking_data_optimized
        result = get_ranking_data_optimized(get_cached_sales_data(), year)
        return jsonify(result)
    except Exception as e:
        app.logger.error(f"获取年度排名数据失败: {str(e)}")
        return jsonify({'success': False, 'error': f'获取年度排名数据失败: {str(e)}'}), 500

@app.route('/api/market-share-data')
def market_share_data():
    year = request.args.get('year', default=2023, type=int)
    try:
        from Python.market_share_data import get_market_share_data_optimized
        result = get_market_share_data_optimized(get_cached_sales_data(), year)
        return jsonify(result)
    except Exception as e:
        app.logger.error(f"获取市场份额数据失败: {str(e)}")
        return jsonify({'success': False, 'error': f'获取市场份额数据失败: {str(e)}'}), 500

@app.route('/api/model-top10-data')
def model_top10_data():
    try:
        from Python.model_top10 import get_model_top10_data
        result = get_model_top10_data(get_cached_sales_data())
        return jsonify(result)
    except Exception as e:
        app.logger.error(f"获取车型前10数据失败: {str(e)}")
        return jsonify({'success': False, 'error': f'获取车型前10数据失败: {str(e)}'}), 500

@app.route('/api/model-manufacturer-top10')
def model_manufacturer_top10():
    try:
        from Python.model_manufacturer_top10 import get_model_manufacturer_top10_data
        result = get_model_manufacturer_top10_data(get_cached_sales_data())
        return jsonify(result)
    except Exception as e:
        app.logger.error(f"获取厂商车型TOP10漏斗图数据失败: {str(e)}")
        return jsonify({'success': False, 'error': f'数据处理失败: {str(e)}'}), 500
    
# 手动刷新缓存的接口
@app.route('/api/refresh-cache')
def refresh_cache():
    try:
        # 清除旧缓存
        _data_cache.clear()
        _cache_timestamp.clear()
        
        # 重新加载数据
        get_cached_sales_data()
        
        return jsonify({'success': True, 'message': '缓存刷新成功'})
    except Exception as e:
        app.logger.error(f"刷新缓存失败: {str(e)}")
        return jsonify({'success': False, 'error': f'刷新缓存失败: {str(e)}'}), 500


# 在 app.py 中添加以下路由

# 获取所有厂商列表
@app.route('/api/manufacturers')
def get_manufacturers():
    try:
        df = get_cached_sales_data()
        manufacturers = df['厂商'].unique().tolist()
        return jsonify({'success': True, 'manufacturers': manufacturers})
    except Exception as e:
        app.logger.error(f"获取厂商列表失败: {str(e)}")
        return jsonify({'success': False, 'error': str(e)}), 500


# 获取指定厂商的车型列表
@app.route('/api/models')
def get_models():
    manufacturer = request.args.get('manufacturer')
    if not manufacturer:
        return jsonify({'success': False, 'error': '缺少厂商参数'}), 400

    try:
        df = get_cached_sales_data()
        models = df[df['厂商'] == manufacturer]['车型'].unique().tolist()
        return jsonify({'success': True, 'models': models})
    except Exception as e:
        app.logger.error(f"获取车型列表失败: {str(e)}")
        return jsonify({'success': False, 'error': str(e)}), 500


# 获取指定厂商、车型的年份列表
@app.route('/api/years')
def get_years():
    manufacturer = request.args.get('manufacturer')
    model = request.args.get('model')
    if not manufacturer or not model:
        return jsonify({'success': False, 'error': '缺少参数'}), 400

    try:
        df = get_cached_sales_data()
        years = df[(df['厂商'] == manufacturer) &
                   (df['车型'] == model)]['年份'].unique().tolist()
        years = sorted(years, reverse=True)  # 从最新到最旧排序
        return jsonify({'success': True, 'years': years})
    except Exception as e:
        app.logger.error(f"获取年份列表失败: {str(e)}")
        return jsonify({'success': False, 'error': str(e)}), 500
    

from Python.top_15_car_sales_pie_chart import get_top15_manufacturer_pie_data
@app.route('/api/top15-manufacturer-data')
def top15_manufacturer_data():
    try:
        df = get_cached_sales_data()
        result = get_top15_manufacturer_pie_data(df)
        return jsonify(result)
    except Exception as e:
        app.logger.error(f"获取top15厂商数据失败: {str(e)}")
        return jsonify({'success': False, 'error': str(e)}), 500

@app.route('/api/model-trend-timeline')
def model_trend_timeline():
    try:
        from Python.area_timeline import get_model_trend_timeline
        result = get_model_trend_timeline(get_cached_sales_data())
        return jsonify(result)
    except Exception as e:
        app.logger.error(f"获取车型趋势时间轴数据失败: {str(e)}")
        return jsonify({'success': False, 'error': f'数据处理失败: {str(e)}'}), 500

@app.route('/api/sales-trend')
def sales_trend():
    try:
        from Python.sales_trend import get_sales_trend
        df = get_cached_sales_data()
        
        # 输出数据样本用于调试
        print("数据样本:", df.head().to_dict(orient='records'))
        print("数据基本信息:", df.info())
        print("日期列类型:", df['日期'].dtype)
        
        result = get_sales_trend(df)
        
        # 输出最终图表数据
        print("图表数据:", json.dumps(result, ensure_ascii=False, default=str))
        
        return jsonify(result)
    except Exception as e:
        import traceback
        app.logger.error(f"获取销售趋势数据失败: {str(e)}\n{traceback.format_exc()}")
        return jsonify({
            'success': False,
            'error': f'数据处理失败: {str(e)}',
            'details': str(e)
        }), 500
    
@app.route('/api/advanced-query')
def advanced_query():
    manufacturer = request.args.get('manufacturer')
    model = request.args.get('model')
    year = request.args.get('year')

    if not manufacturer or not model or not year:
        return jsonify({'success': False, 'error': '参数不完整'}), 400

    try:
        year = int(year)  # 转换为整数
        df = get_cached_sales_data()

        # 查询当年数据
        current_data = df[(df['厂商'] == manufacturer) &
                          (df['车型'] == model) &
                          (df['年份'] == year)]

        # 计算当年总销量 - 转换为 Python int
        total_sales = int(current_data['销量'].sum())  # 转换为 Python int

        # 查询去年数据
        last_year_data = df[(df['厂商'] == manufacturer) &
                            (df['车型'] == model) &
                            (df['年份'] == year - 1)]
        last_year_sales = last_year_data['销量'].sum()

        # 计算同比变化
        if last_year_sales > 0:
            change_percent = ((total_sales - last_year_sales) / last_year_sales) * 100
            change_text = f"{change_percent:+.2f}%"
        else:
            change_text = "N/A (去年无数据)"

        # 计算排名（该车型在全部车型中的年度排名）
        # 先计算所有车型的年度总销量
        df_year = df[df['年份'] == year]
        model_sales = df_year.groupby(['厂商', '车型'])['销量'].sum().reset_index()
        model_sales = model_sales.sort_values('销量', ascending=False)
        model_sales['rank'] = range(1, len(model_sales) + 1)

        # 查找当前车型的排名 - 转换为 Python int 或字符串
        rank_series = model_sales.loc[
            (model_sales['厂商'] == manufacturer) &
            (model_sales['车型'] == model),
            'rank'
        ]

        # 正确处理排名结果
        if not rank_series.empty:
            rank = int(rank_series.values[0])  # 转换为 Python int
        else:
            rank = "未进入排名"

        # 返回结果 - 确保所有数据类型都是 JSON 可序列化的
        return jsonify({
            'success': True,
            'result': {
                'manufacturer': manufacturer,
                'model': model,
                'year': year,
                'total_sales': total_sales,  # 已经是 Python int
                'rank': rank,  # Python int 或字符串
                'change_percent': change_text
            }
        })
    except Exception as e:
        app.logger.error(f"高级查询失败: {str(e)}")
        return jsonify({'success': False, 'error': str(e)}), 500
    
@app.before_request
def ensure_cache_loaded():
    """确保缓存已加载"""
    global _cache_initialized
    if not _cache_initialized:
        warm_up_cache()

if __name__ == '__main__':
    logging.basicConfig(level=logging.INFO)
    app.run(debug=True, threaded=True)